/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package is.bean.region;

import is.bean.BeanRoutines;
import java.sql.*;
import java.util.Collection;
import java.util.LinkedList;
import javax.ejb.*;

/**
 *
 * @author aspr0
 */
public class RegionBean extends BeanRoutines implements EntityBean
{
    private Region data = new Region();

    @Override
    public void setEntityContext(EntityContext aContext)
    {
        super.setEntityContext(aContext);
        tableName = "region";
    }

    /**
     * @see javax.ejb.EntityBean#ejbLoad()
     */
    public void ejbLoad()
    {
        try
        {
            data = loadData();
        }
        catch (SQLException ex)
        {
            throw new EJBException(ex.getMessage());
        }
    }

    /**
     * @see javax.ejb.EntityBean#ejbStore()
     */
    public void ejbStore()
    {
        try
        {
            storeData(data);
        }
        catch (SQLException ex)
        {
            throw new EJBException(ex.getMessage());
        }
    }

    public Integer ejbCreateInstance(Region data) throws CreateException, SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        Integer createdId = null;
        ResultSet resultSet = null;

        try
        {
            String sql = "select region_seq.nextval as id from dual";

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            resultSet.next();
            createdId = resultSet.getInt("id");

            sql = "insert"
                    + " into region "
                    + "(id, name, parentId) "
                    + "values "
                    + "(?, ?, ?) ";
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);

            statement.setInt(1, createdId);
            setStringNullDecide(statement, 2, data.getName());
            setIntNullDecide(statement, 3, data.getParentId());

            statement.execute();
        }
        catch (SQLException e)
        {
//            e.printStackTrace();
            throw new CreateException();
        }
        finally
        {
            if (statement != null)
            {
                statement.close();
            }

            if (connection != null)
            {
                connection.close();
            }

            if (resultSet != null)
            {
                resultSet.close();
            }
        }

        this.data = data;
        return createdId;
    }

    public void ejbPostCreateInstance(Region data)
    {
    }

    public Collection ejbFindByExample(Region example) throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection<Integer> foundIds = new LinkedList<Integer>();

        try
        {
            Integer idE = example.getId();
            String nameE = example.getName();
            Integer parentIdE = example.getParentId();

            String sql = "select id from region %s";

            String where = "";
            String delimiter = "and ";

            if (idE != null)
            {
                where += "regexp_like(id, ?) ";
            }
            if (nameE != null)
            {
                where += (where.length() > 0) ? delimiter : "" + "regexp_like(name, ?) ";
            }
            if (parentIdE != null)
            {
                where += (where.length() > 0) ? delimiter : "" + "parentId = ? ";
            }
            where = ((where.length() > 0) ? "where " : "") + where;

            sql = String.format(sql, where);

            connection = dataSource.getConnection();
            statement = connection.prepareStatement(sql);
            Integer parameterIndex = 1;

            if (idE != null)
            {
                statement.setInt(parameterIndex++, idE);
            }
            if (nameE != null)
            {
                statement.setString(parameterIndex++, nameE);
            }
            if (parentIdE != null)
            {
                statement.setInt(parameterIndex++, parentIdE);
            }

            resultSet = statement.executeQuery();

            while (resultSet.next())
            {
                foundIds.add(resultSet.getInt("id"));
            }

        }
        finally
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }

            if (connection != null)
            {
                connection.close();
            }
        }

        return foundIds;
    }

    public Region getData()
    {
        return data;
    }

    public void setData(Region data)
    {
        this.data = data;
        modified = true;
    }

    private Region loadData() throws SQLException
    {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try
        {
            connection = dataSource.getConnection();
            String sql = "select * from region where id = ?";
            statement = connection.prepareStatement(sql);

            id = (Integer) context.getPrimaryKey(); //todo resolve primary key after ejbCreate unset

            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            resultSet.next();

            data.setId(resultSet.getInt("id"));
            data.setName(resultSet.getString("name"));
            data.setParentId(resultSet.getInt("parentId"));
        }
        finally
        {
            if (resultSet != null)
            {
                resultSet.close();
            }

            if (statement != null)
            {
                statement.close();
            }
            if (connection != null)
            {
                connection.close();
            }

            return data;
        }
    }

    private void storeData(Region data) throws SQLException
    {
        if (modified)
        {
            Connection connection = null;
            PreparedStatement statement = null;

            try
            {
                connection = dataSource.getConnection();
                String sql = "update region set name = ?, parentId = ? where id = ?";
                statement = connection.prepareStatement(sql);

                setStringNullDecide(statement, 1, data.getName());
                setIntNullDecide(statement, 2, data.getParentId());
                setIntNullDecide(statement, 3, id);
                statement.execute();
            }
            finally
            {
                if (statement != null)
                {
                    statement.close();
                }

                if (connection != null)
                {
                    connection.close();
                }
            }
        }
    }
}
